/****** Object:  Stored Procedure BulkOrdersGetList    Script Date: Thursday, February 21, 2008 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spBulkOrdersGetList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [spBulkOrdersGetList]
GO

	
	
	
/******************************************************************************
**		File: 
**		Name: spBulkOrdersGetList
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 2/21/2008 2:16:35 AM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE spBulkOrdersGetList
	@OrderBy varchar(50),
	@OrderDirection varchar(5),
	@Page int,
	@PageSize int,
	@TotalRecords int output
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #TempBulkOrders (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	BulkOrderID int	
)

DECLARE @sql nvarchar(2000)

-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #TempBulkOrders ([BulkOrderID]) SELECT '
IF (@PageSize > 0)
	SET @sql = @sql + ' TOP ' + CAST(@Page*@PageSize as nvarchar) 

SET @sql = @sql + ' [BulkOrderID] FROM [BulkOrders]  ORDER BY [' + @OrderBy + N'] ' + @OrderDirection

EXEC (@sql)

SELECT @TotalRecords = COUNT(*) FROM [BulkOrders]

SELECT
	[BulkOrders].[BulkOrderID],
	[OrderDate],
	[Type],
	[ProductCode],
	[Description],
	[Color],
	[StartDate],
	[TotalQtyOrdered],
	[TotalQtyCompleted],
	[TotalQtyInProgressBelow50P],
	[TotalQtyInProgressAbove50P],
	[EstimatedDate],
	[StockLastCheckedDate],
	[StockLastCheckedQty],
	[FOB],
	[CompletedDate],
	[SupplierCode],
	[Owner],
	[Catalogue],
	[Page],
	[Pack],
	[PackQty],
	[CatalogID],
	[Target],
	[SalesDescription1],
	[SalesDescription2],
	[StockLocation],
	[AvailableInUSA],
	[BONo],
	[MinOrderQty],
	[TallPaletQty],
	[IDSSortID],
	[IsArchived],
	[CatPackDesc],
	[MUKTHGCatPackQty],
	[MUKTHGCatPackDesc],
	[HomeDelivered15Price],
	[CurrencyID],
	[IsShownInLGDCatalog],
	[SpecialOfferDisc],
	[ModifiedDate]
FROM
	#TempBulkOrders AS tblTemp JOIN [BulkOrders] ON
	tblTemp.BulkOrderID = [BulkOrders].BulkOrderID 	
WHERE (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)
ORDER BY RowNumber

DROP TABLE #TempBulkOrders

GO

	
